import  time,pymysql,datetime
import requests,os,re
# from crawlab import save_item
from requests_html import HTMLSession

conn = pymysql.connect(
    host="172.16.10.201",
    user="zy001",
    port=3306,
    password="zy@123",
    database="crawl-bk",
    charset = 'utf8'
)
cursor = conn.cursor()
sql = "select url from bieke_chengjiao"

sql2 = '''insert into bieke_chengjiao_detail(title,url,deal_money,deal_price,list_money,
deal_cycle,change_times,show_times,followers,view_num,layout,layer_info,
area,room_struct,type_area,building_type,orientation,build_year,fitment,
building_struct,heating,elevator_ration,elevator,lianjia_id,propertys,
list_time,purpose,house_age,ownership,key_int,community_int,room_int,
surrounding,appropriate_crowd,building_describe,create_time)
 values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'''


if __name__ == '__main__':
    session = HTMLSession()
    cursor.execute(sql)
    data = cursor.fetchall()

    header = {
        'User-Agent':'Mozilla/5.0(Macintosh;Intel Mac OS X 10_15_7)AppleWebKit/537.36(KHTML,likeGecko)Chrome/96.0.4664.110Safari/537.36',
        'Cookie': 'HMACCOUNT_BFESS=A1ADBCFE718283FD; BCLID_BFESS=10960042313393804416; BDSFRCVID_BFESS=73POJexroG0RzHjDKqAjUrJZlmKKb-bTDYrEOwXPsp3LGJLVgalNEG0PtOU-nzF-oxjCogKKX2OTHIIF_2uxOjjg8UtVJeC6EG0Ptf8g0M5; H_BDCLCKID_SF_BFESS=tbAD_IIMtDK3HnRY-P4_-tAt2qoXetJyaR39BCJvWJ5TMCoYQ6QCylDv3nOAhpQHWHQQ_DQC2J0-ShPC-tnAQ6JQ3JOK-jcZB23K_h793l02V56Ie-t2yT0V0lrZQ-RMW23voq7mWpTzsxA45J7cM4IseboJLfT-0bc4KKJxbnLWeIJIjjC5e5OBjaKJJjnXa5n-stTObRbHDTrnhPF3WlFfXP6-35KHbmQMan4K3po5jPQaKx6AhM-PXfOuWh37JD6yWxcNBhcDshQ426raDh0m3toxJp8tBRbMopvaBx36OxjvbURvW5Dg3-7ABx5dtjTO2bc_5KnlfMQ_bf--QfbQ0hOhqP-j5JIEoD8-fIL-hI-r-R-_-4_tbh_X5-RLfKTeKp7F54nKDp0x0lrmXU0uqqCOLRjZyDt8-lTLJh7xsMTsQfnbWh8yKabr0MTrQDvIhRjN3KJmfKn1bUbA5tD_2-nZ2-biWa-H2MbdWlTP_IoG2Mn8M4bb3qOpBtQmJeTxoUJ25DnJhhCGe6DaDTbbeH_s-bbfHD6eL488Kb7VKROkenOpyU4pbt-qJJvpyHnPLqbc5JoSSto_W4vVyTLeKf6nBT5KaaOR2nFyQpOGshQvQbC--PIkQN3Tb-KO5bRiL661tMtWDn3oypAKXp0nytrly5jtMgOBBJ0yQ4b4OR5JjxonDh83bG7MJPKtfJCfVCtbtI83H48k-4QEbbQH-UnLq5-eLgOZ04n-ah02V4cSMxOvKf_q3q5ZXt7LW23asRom3UTKsq76Wh35K5tTQP6rLtbKago4KKJxbp7lKJruQCco2-IdhUJiBhkHBan7alTIXKohJh7FM4tW3J0ZyxomtfQxtNRJ0DnjtpChbRO4-TFajjO-eMK; BAIDUID_BFESS=347A7F18201CA68524C7DF383F217858:FG=1; ab_sr=1.0.1_ZTEwODY3YTI3MDNiMWM2NWViYWVmZDI3NDU5NmM5NWQ4NDJlMWNlMTAxM2IxYWVlNzhhMzAwN2QyNzYxNWQ3MjRiYWRhMzM2NTFjOGE5NWI3OTVjNTdjZTc4MDI4ZWRhZmQwMGI1OGE1NTcxMDJkMGYwMzllYjU1ZDU4NTE0NWU0OGFmY2IxMWRmZGU5NjI3MmVmYjA1YmU1MmUxNDg2Yg=='}

    req = session.get(url=' https://km.ke.com/chengjiao/106109369675.html',headers=header).html


    for i in data:
        try:
            data_dict ={}
            req = session.get(i[0],headers=header).html
            time.sleep(1)
            data_dict['title'] = req.xpath('//*[@class="main"]/text()',first=True).strip() if req.xpath('//*[@class="main"]/text()',first=True) else None
            data_dict['url'] = i[0]
            data_dict['deal_money'] = req.xpath('//span[@class="dealTotalPrice"]/i/text()',first=True).strip() if req.xpath('//span[@class="dealTotalPrice"]/i/text()',first=True) else None
            data_dict['deal_price'] = req.xpath('//div[@class="price"]/b/text()',first=True).strip() if req.xpath('//div[@class="price"]/b/text()',first=True) else None
            msg_list = req.find('div.msg>span')
            msgs = {'挂牌价格':'list_money','成交':'deal_cycle','调价':'change_times','带看':'show_times',
                    '关注':'followers','浏览':'view_num','房屋户型':'layout','所在楼层':'layer_info',
                    '建筑面积':'area','户型结构':'room_struct','套内面积':'type_area','建筑类型':'building_type',
                    '房屋朝向':'orientation','建成年代':'build_year','装修情况':'fitment','建筑结构':'building_struct',
                    '供暖方式':'heating','梯户比例':'elevator_ration','配备电梯':'elevator','链家编号':'lianjia_id',
                    '交易权属':'propertys','挂牌时间':'list_time','房屋用途':'purpose','房屋年限':'house_age',
                    '房权所属':'ownership','核心卖点':'key_int','小区介绍':'community_int','户型介绍':'room_int',
                    '周边配套':'surrounding','适宜人群':'appropriate_crowd','装修描述':'building_describe'}
            for i in msg_list:
                for k,v  in msgs.items():
                    if k in i.text:
                        data_dict[v] = i.xpath('//label',first=True).text
            basic_info = req.find('.content>ul>li')
            for i in basic_info:
                for k,v  in msgs.items():
                    label = i.find('span',first=True).text
                    if k in i.text:
                        data_dict[v] = i.text.split(label)[1]
            Housing = req.find('.baseattribute')
            for i in Housing:
                for k,v  in msgs.items():
                    label = i.find('.name',first=True).text
                    if k in i.text:
                        data_dict[v] = i.find('.content',first=True).text
            data_dict['create_time'] = datetime.datetime.now()
            
            a= (data_dict.get('title'),data_dict.get('url'),data_dict.get('deal_money'),data_dict.get('deal_price'),data_dict.get('list_money'),
                data_dict.get('deal_cycle'),data_dict.get('change_times'),data_dict.get('show_times'),data_dict.get('followers'),data_dict.get('view_num'),data_dict.get('layout'),data_dict.get('layer_info'),
                data_dict.get('area'),data_dict.get('room_struct'),data_dict.get('type_area'),data_dict.get('building_type'),data_dict.get('orientation'),data_dict.get('build_year'),data_dict.get('fitment'),
                data_dict.get('building_struct'),data_dict.get('heating'),data_dict.get('elevator_ration'),data_dict.get('elevator'),data_dict.get('lianjia_id'),data_dict.get('propertys'),
                data_dict.get('list_time'),data_dict.get('purpose'),data_dict.get('house_age'),data_dict.get('wnership'),data_dict.get('key_int'),data_dict.get('community_int'),data_dict.get('room_int'),
                data_dict.get('surrounding'),data_dict.get('appropriate_crowd'),data_dict.get('building_describe'),data_dict.get('create_time'))
            # save_item(data_dict)
            print(a)
            cursor.execute(sql2,a)
            conn.commit()
        except Exception as e:
            print(e)
            time.sleep(20)
     




